ALTER PROC SP_CREATE_UPDATE_CUSTOMER_HOLDS
(
	@CUSTOMER_ID 	INT,
	@USER_NAME 	VARCHAR(50),
	@MARKETING_HOLD VARCHAR(4),
	@FINANCE_HOLD 	VARCHAR(4),
	@OPERATION_HOLD VARCHAR(4),
	@SALES_HOLD 	VARCHAR(4),
	@IS_SAMPLE 	BIT
)
AS
BEGIN
	DECLARE @M_IS_APPROVED BIT
	DECLARE @F_IS_APPROVED BIT
	DECLARE @O_IS_APPROVED BIT
	DECLARE @S_IS_APPROVED BIT

	SET @M_IS_APPROVED = CASE WHEN ( @MARKETING_HOLD IS NULL OR @MARKETING_HOLD = '' ) THEN 0 ELSE 1 END
	SET @F_IS_APPROVED = CASE WHEN ( @FINANCE_HOLD IS NULL OR @FINANCE_HOLD = '' ) THEN 0 ELSE 1 END
	SET @O_IS_APPROVED = CASE WHEN ( @OPERATION_HOLD IS NULL OR @OPERATION_HOLD = '' ) THEN 0 ELSE 1 END
	SET @S_IS_APPROVED = CASE WHEN ( @SALES_HOLD IS NULL OR @SALES_HOLD = '' ) THEN 0 ELSE 1 END

	IF( ( SELECT COUNT(*) FROM Customer_Hold_Table WHERE CUSTOMER_ID = @CUSTOMER_ID AND Hold_By_Group = 'OPERATIONS' AND IS_SAMPLE = @IS_SAMPLE ) = 0 )
	BEGIN
		INSERT INTO Customer_Hold_Table( Customer_ID, Hold_Reason_Code, Hold_By_Group, Hold_By_User, Is_Approved, Timestamp, IS_SAMPLE ) 
		VALUES ( @CUSTOMER_ID, @OPERATION_HOLD, 'OPERATIONS', @USER_NAME, @O_IS_APPROVED, CURRENT_TIMESTAMP, @IS_SAMPLE )
	END
	ELSE IF( ( SELECT COUNT(*) FROM Customer_Hold_Table WHERE CUSTOMER_ID = @CUSTOMER_ID AND Hold_By_Group = 'OPERATIONS' AND IS_SAMPLE = @IS_SAMPLE ) > 0 )
	BEGIN
		UPDATE Customer_Hold_Table SET Hold_Reason_Code = @OPERATION_HOLD, Is_Approved = @O_IS_APPROVED, Hold_By_User = @USER_NAME, Timestamp = CURRENT_TIMESTAMP 
		WHERE CUSTOMER_ID = @CUSTOMER_ID AND Hold_By_Group = 'OPERATIONS'  AND IS_SAMPLE = @IS_SAMPLE
	END

	IF( ( SELECT COUNT(*) FROM Customer_Hold_Table WHERE CUSTOMER_ID = @CUSTOMER_ID AND Hold_By_Group = 'FINANCE' AND IS_SAMPLE = @IS_SAMPLE ) = 0 )
	BEGIN
		INSERT INTO Customer_Hold_Table( Customer_ID, Hold_Reason_Code, Hold_By_Group, Hold_By_User, Is_Approved, Timestamp, IS_SAMPLE ) 
		VALUES ( @CUSTOMER_ID, @FINANCE_HOLD, 'FINANCE', @USER_NAME, @F_IS_APPROVED, CURRENT_TIMESTAMP, @IS_SAMPLE)
	END
	ELSE IF( ( SELECT COUNT(*) FROM Customer_Hold_Table WHERE CUSTOMER_ID = @CUSTOMER_ID AND Hold_By_Group = 'FINANCE' AND IS_SAMPLE = @IS_SAMPLE ) > 0 )
	BEGIN
		UPDATE Customer_Hold_Table SET Hold_Reason_Code = @FINANCE_HOLD, Is_Approved = @F_IS_APPROVED, Hold_By_User = @USER_NAME, Timestamp = CURRENT_TIMESTAMP 
		WHERE CUSTOMER_ID = @CUSTOMER_ID AND Hold_By_Group = 'FINANCE'  AND IS_SAMPLE = @IS_SAMPLE
	END

	IF( ( SELECT COUNT(*) FROM Customer_Hold_Table WHERE CUSTOMER_ID = @CUSTOMER_ID AND Hold_By_Group = 'MARKETING' AND IS_SAMPLE = @IS_SAMPLE ) = 0 )
	BEGIN
		INSERT INTO Customer_Hold_Table( Customer_ID, Hold_Reason_Code, Hold_By_Group, Hold_By_User, Is_Approved, Timestamp, IS_SAMPLE ) 
		VALUES ( @CUSTOMER_ID, @MARKETING_HOLD, 'MARKETING', @USER_NAME, @M_IS_APPROVED, CURRENT_TIMESTAMP, @IS_SAMPLE)
	END 
	ELSE IF( ( SELECT COUNT(*) FROM Customer_Hold_Table WHERE CUSTOMER_ID = @CUSTOMER_ID AND Hold_By_Group = 'MARKETING' AND IS_SAMPLE = @IS_SAMPLE ) > 0 )
	BEGIN
		UPDATE Customer_Hold_Table SET Hold_Reason_Code = @MARKETING_HOLD, Is_Approved = @M_IS_APPROVED, Hold_By_User = @USER_NAME, Timestamp = CURRENT_TIMESTAMP 
		WHERE CUSTOMER_ID = @CUSTOMER_ID AND Hold_By_Group = 'MARKETING'  AND IS_SAMPLE = @IS_SAMPLE
	END

	IF( ( SELECT COUNT(*) FROM Customer_Hold_Table WHERE CUSTOMER_ID = @CUSTOMER_ID AND Hold_By_Group = 'SALES' AND IS_SAMPLE = @IS_SAMPLE ) = 0 )
	BEGIN
		INSERT INTO Customer_Hold_Table( Customer_ID, Hold_Reason_Code, Hold_By_Group, Hold_By_User, Is_Approved, Timestamp, IS_SAMPLE ) 
		VALUES ( @CUSTOMER_ID, @SALES_HOLD, 'SALES', @USER_NAME, @S_IS_APPROVED, CURRENT_TIMESTAMP, @IS_SAMPLE)
	END
	ELSE IF( ( SELECT COUNT(*) FROM Customer_Hold_Table WHERE CUSTOMER_ID = @CUSTOMER_ID AND Hold_By_Group = 'SALES' AND IS_SAMPLE = @IS_SAMPLE ) > 0 )
	BEGIN
		UPDATE Customer_Hold_Table SET Hold_Reason_Code = @SALES_HOLD, Is_Approved = @S_IS_APPROVED, Hold_By_User = @USER_NAME, Timestamp = CURRENT_TIMESTAMP 
		WHERE CUSTOMER_ID = @CUSTOMER_ID AND Hold_By_Group = 'SALES'  AND IS_SAMPLE = @IS_SAMPLE
	END
END